/**   
* @{#} MonthAccount.java Create on 2013-6-27 下午7:56:56   
*   
* Copyright (c) 2012 by Baidu.   
*/
package union.test;

import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import objtable.algm.GroupBy;
import objtable.core.TableCenter;
import objtable.core.TableGenerator;
import objtable.port.DbPort;
import objtable.struct.DataType;
import objtable.struct.Row;

/**   
* @author <a href="mailto:zhangbo07@baidu.com">zhangbo07</a>  
* @version 1.0   
*/

public class MonthAccount {
	public static void main(String[] args) {
		String month = "201304";
		//329业务库
		DbPort db_main = new DbPort("jdbc:mysql://10.81.22.47:8910/union4",
				"union", "union");
		//用户表
		TableGenerator tg = new TableGenerator("customer")
				.addField(DataType.INT, "customerid")
				.addField(DataType.INT, "paytype")
				.addField(DataType.INT, "keepmoney")
				.addField(DataType.INT, "identitystatus")
				.addField(DataType.INT, "invoicecheckstatus")
				.addField(DataType.INT, "finstate");
		tg.regToCenter();
		db_main.fromDB(
				"customer",
				"select customerid,paytype,keepmoney,finstate,identitystatus,invoicecheckstatus from customer where userstatus in (4,6) and candivide=1 or customerid in (121917,333368,344540)");

		//付款方式表
		tg = new TableGenerator("paymentunit").addField(DataType.INT,
				"paymentunitid").addField(DataType.INT, "customerid");
		tg.regToCenter();
		db_main.fromDB(
				"paymentunit",
				"select cf.customerid,cf.paymentunitid from customerfinpaymentinfo cf,paymentunit p where cf.paymentunitid=p.paymentunitid");

		//业务对应表
		tg = new TableGenerator("unionbusiness")
				.addField(DataType.INT, "businessid")
				.addField(DataType.STRING, "monthtablename")
				.addField(DataType.INT, "paymentunitid");
		tg.regToCenter();
		db_main.fromDB("unionbusiness",
				"select paymentunitid,businessid,monthtablename from unionbusiness");

		//分月数据表
		tg = new TableGenerator("monthsearchsttt")
				.addField(DataType.INT, "customerid")
				.addField(DataType.INT, "cid")
				.addField(DataType.DOUBLE, "divide");
		tg.regToCenter();
		db_main.fromDB(
				"monthsearchsttt",
				"select a.customerid,a.cid,a.divide from monthsearchsttt a,cn b where a.cid = b.cid and b.cnstatus != 7 and countmonth="
						+ month);

		tg = new TableGenerator("monthsoftwaresttt")
				.addField(DataType.INT, "customerid")
				.addField(DataType.INT, "cid")
				.addField(DataType.DOUBLE, "divide");
		tg.regToCenter();
		db_main.fromDB(
				"monthsoftwaresttt",
				"select a.customerid,a.cid,a.divide from monthsoftwaresttt a,cn b where a.cid = b.cid and b.cnstatus != 7 and countmonth="
						+ month);

		tg = new TableGenerator("monthcprosttt")
				.addField(DataType.INT, "customerid")
				.addField(DataType.INT, "cid")
				.addField(DataType.DOUBLE, "divide");
		tg.regToCenter();
		db_main.fromDB(
				"monthcprosttt",
				"select a.customerid,a.cid,a.divide from monthcprosttt a,cn b where a.cid = b.cid and b.cnstatus != 7 and countmonth="
						+ month);

		tg = new TableGenerator("monthspsttt")
				.addField(DataType.INT, "customerid")
				.addField(DataType.INT, "cid")
				.addField(DataType.DOUBLE, "divide");
		tg.regToCenter();
		db_main.fromDB(
				"monthspsttt",
				"select a.customerid,a.cid,a.divide from monthspsttt a,cn b where a.cid = b.cid and b.cnstatus != 7 and countmonth="
						+ month);

		tg = new TableGenerator("monthappsttt")
				.addField(DataType.INT, "customerid")
				.addField(DataType.INT, "cid")
				.addField(DataType.DOUBLE, "divide");
		tg.regToCenter();
		db_main.fromDB(
				"monthappsttt",
				"select a.customerid,a.cid,a.divide from monthappsttt a,cn b where a.cid = b.cid and b.cnstatus != 7 and countmonth="
						+ month);

		tg = new TableGenerator("monthdansttt")
				.addField(DataType.INT, "customerid")
				.addField(DataType.INT, "cid")
				.addField(DataType.DOUBLE, "divide");
		tg.regToCenter();
		db_main.fromDB(
				"monthdansttt",
				"select a.customerid,a.cid,a.divide from monthdansttt a,cn b where a.cid = b.cid and b.cnstatus != 7 and countmonth="
						+ month);

		tg = new TableGenerator("monthtuansttt")
				.addField(DataType.INT, "customerid")
				.addField(DataType.INT, "cid")
				.addField(DataType.DOUBLE, "divide");
		tg.regToCenter();
		db_main.fromDB(
				"monthtuansttt",
				"select a.customerid,a.cid,a.divide from monthtuansttt a,cn b where a.cid = b.cid and b.cnstatus != 7 and countmonth="
						+ month);

		tg = new TableGenerator("monthbdxsttt")
				.addField(DataType.INT, "customerid")
				.addField(DataType.INT, "cid")
				.addField(DataType.DOUBLE, "divide");
		tg.regToCenter();
		db_main.fromDB(
				"monthbdxsttt",
				"select a.customerid,a.cid,a.divide from monthbdxsttt a,cn b where a.cid = b.cid and b.cnstatus != 7 and countmonth="
						+ month);

		//分用户汇总数据
		tg = new TableGenerator("tmp_business")
				.addField(DataType.INT, "customerid")
				.addField(DataType.INT, "paymentunitid")
				.addField(DataType.INT, "businessid")
				.addField(DataType.DOUBLE, "divide");
		tg.regToCenter();
		List<Row> tmp_busi = new ArrayList<Row>();
		//TransBy tb = new TransBy("tmp_business", "customerid,divide");
		GroupBy gb = new GroupBy("customerid,paymentunitid", "divide");

		//用户减扣
		tg = new TableGenerator("userdividededuce")
				.addField(DataType.INT, "customerid")
				.addField(DataType.INT, "paymentunitid")
				.addField(DataType.DOUBLE, "deduction")
				.addField(DataType.STRING, "remark");
		tg.regToCenter();
		db_main.fromDB(
				"userdividededuce",
				"select customerid,paymentunitid,deduction,remark from userdividededuce where status=0");

		//未付金额
		tg = new TableGenerator("account").addField(DataType.INT, "customerid")
				.addField(DataType.INT, "paymentunitid")
				.addField(DataType.DOUBLE, "notpay");
		tg.regToCenter();
		db_main.fromDB("account",
				"select customerid,paymentunitid,notpay from account");

		//---------------------------主程序开始--------------------------------
		Logger log = LoggerFactory.getLogger(MonthAccount.class);
		List<Row> l_customer = TableCenter.getTableData("customer");
		log.info("l_customer:" + l_customer.size());
		for (Row cus : l_customer) {
			int customerid = cus.getInt("customerid");
			List<Row> l_paymentid = TableCenter.filterTableData("paymentunit",
					"customerid==" + customerid);
			log.info("customer:" + customerid + " paymentunits:"
					+ l_paymentid.size());

			tmp_busi.clear();
			for (Row payment : l_paymentid) {
				List<Row> l_unionbusiness = TableCenter.filterTableData(
						"unionbusiness",
						"paymentunitid==" + payment.get("paymentunitid"));

				for (Row unionbiz : l_unionbusiness) {
					List<Row> montht = TableCenter.filterTableData(
							unionbiz.getString("monthtablename"),
							"customerid==" + customerid);
					//List<? extends Row> all = tb.execute(montht);
					//					for (Row t : all) {
					//						t.set("paymentunitid", payment.get("paymentunitid"));
					//						t.set("businessid", unionbiz.get("businessid"));
					//					}
					//					tmp_busi.addAll(all);
				}
			}

			//用户按paymentunitid汇总
			List<? extends Row> sums = gb.execute(tmp_busi);
			for (Row p_sum : sums) {
				//批量减扣
				List<Row> redeuce = TableCenter.filterTableData(
						"userdividededuce",
						"customerid==" + customerid + " && paymentunitid=="
								+ p_sum.get("paymentunitid"));
				for (Row rede : redeuce) {
					p_sum.set(
							"divide",
							p_sum.getDouble("divide")
									- rede.getDouble("deduction"));
				}
				//加上保留付款金额
				List<Row> notpay = TableCenter.filterTableData("account",
						"customerid==" + customerid + " && paymentunitid=="
								+ p_sum.get("paymentunitid"));
				for (Row nopay : notpay) {
					p_sum.set(
							"divide",
							p_sum.getDouble("divide")
									+ nopay.getDouble("notpay"));
				}

			}
		}
	}
}
